<? // data-dictionary - saved as json exported to browser

$dd_ver = "1.00";

$dd_raw = Array(

	// defaults - client dd only
	'*' => Array(
		// column name regx
		'col' => Array(
			'^name$' => Array( 's.width'=>'180px', 'required'=>true, 'label'=>'Name' ),
			'^name_' => Array( 's.width'=>'180px' ),
			'^pwd_' => Array( 's.width'=>'180px' ),
			'_id$' => Array( 'datatype'=>'number', 'type'=>'hidden' ),
			'_nm$' => Array( 's.width'=>'180px' ),
			'_sq$' => Array( 's.width'=>'39px' ),
			'_cd$' => Array( 'datatype'=>'code', 's.width'=>'75px' ),
			'_dt$' => Array( 's.width'=>'92px' ),
			'_tm$' => Array( 's.width'=>'60px' ),
			'^login$' => Array( 's.width'=>'75px' ),
			'^active$' => Array( 'datatype'=>'boolean', 'label'=>'Active', 'default'=>1 ) ),
		// datatype match
		'datatype' => Array(
			'boolean' => Array( 'notnull'=>true, 'value'=>0, 'type'=>'checkbox' ),
			'code' => Array( 'pattern'=>'^[a-z_][a-z0-9_]*$' ),
			'password' => Array( 'type'=>'password' ),
			'number' => Array( 'type'=>'number'  ),
			'date' => Array( 'type'=>'date' ),
			'datetime' => Array( 'type'=>'datetime' ),
			'time' => Array( 'type'=>'time', 'step'=>'60' ),
			'url' => Array( 'type'=>'url' ),
			'text' => Array( 'type'=>'text' ) ),
	),

	// meta - should not be changed
	'~' => Array(
		// true/false attributes - say "noattribute" for false, eg, "noreadonly"
		'truthies' => Array( 'autocomplete','checked','disabled','readonly', 'required',
				'helper','lookup','validate','calc','post','update','embold',
				'notnull','updateable','unique','validate' ),
		'changeEvents' => Array( 'helper','lookup','validate','post','update','embold' ),
		// all dd attributes
		'universe' => Array(
			'rel' => Array( 'class','name','dd','tbl','tbl_v','pkcol','dml','request','update','embold', 
					'helper','lookup','validate','post','update','embold' ),
			'tup' => Array( 'class','pkval','key','dml','request','update','embold' ),
			'att' => Array( 'autocomplete','checked','class','disabled','form','pattern','placeholder','readonly', 'required','type',
					'helper','lookup','validate','calc','post','update','embold',
					'datatype','default','enum','col','notnull','label','doCalc',
					'dml','request','unique','updateable', 'depon', 'fkcol', 'refCol' ) ),
		// triggers - set when not false
		//~'triggers' => Array(	'update'=>'change', 'validate'=>'change', 'embold'=>'change', 'lookup'=>'change' ),
		'triggers' => Array(),
		// rendered to dom
		'render' => Array(
			'rel' => Array( 'dd','tbl','tbl_v','pkcol','dml','update','request','embold' ),
			'tup' => Array( 'pkval','key','dml','update','request','embold' ),
			'att' => Array( 'autocomplete','checked','class','disabled','form','pattern','placeholder','readonly', 'required','type','step',
					'col', 'dml','request', 'updateable', 'depon', 'enum', 'doCalc',
					'helper','lookup','validate','calc','post','update','embold' ),
			'noneditable' => Array( 'class', 'col', 'dml', 'update', 'updateable', 'depon', 'calc', 'doCalc' ),
		),
		// posted to server
		'post' => Array( 
			'rel' => Array( 'dd','tbl','tbl_v','pkcol','dml','update','request' ),
			'tup' => Array( 'pkval','key','dml','update','request' ),
			'att' => Array( 'col','dml','update','request', 'value', 'dataValue' ) ),
		// client dd - todo
		'client' => Array(
			'rel' => Array( 'dd','name','pkcol','dml','validate','update','request' ),
			'tup' => Array( 'dml','update','request','dml' ),
			'att' => Array( 'class','datatype','default','enum','col','label',
					'dml','request','required','updateable', 'depon', 'doCalc',
					'helper','lookup','validate','calc','post','update','embold' ) ),
		// server dd - todo
		'server' => Array(
			'rel' => Array( 'dd','name','tbl','tbl_v','pkcol','dml','update' ),
			'tup' => Array( ),
			'att' => Array( 'datatype','default','label', 'notnull','col', 'fkcol', 'refCol' ) )
	),

	// relations
	'usr' => Array(
		'tbl' => 'users',
		'tbl_v' => 'users_v',
		'name' => 'Login Users',
		'securityLv' => 4,
		'pkcol' => 'usr_id',
		'orderBy' => 'active desc, login',
		'cols' => Array(
			'login' => Array( 'required' =>true, 'unique' => true, 'label' => 'Login', 'validate' =>true ),
			'pwd' => Array( 'required' =>true, 'datatype'=>'password', 'label' => 'Password' ),
			'name' => Array(),
			'active' => Array(),
			'admin' => Array( 'datatype' => 'boolean', 'label' => 'Administrator' ),
			'superuser' => Array( 'datatype' => 'boolean', 'label' => 'Superuser' ),
		),
	),
	'emp' => Array(
		'tbl' => 'employee',
		'tbl_v' => 'employee_v',
		'name' => 'Employees',
		'securityLv' => 3,
		'pkcol' => 'emp_id',
		'nmcol' => 'disp_name',
		'orderBy' => 'active desc, emp_cd',
		'lookups' => Array( 'emp_cd'=>'exact', 'name'=>'partial' ),
		'cols' => Array(
			'emp_cd' => Array( 'required' =>true, 'unique' => true, 'label' => 'NickName', 'validate' =>true, 'placeholder' => 'Gai' ),
			'name' => Array( 'placeholder' => 'Orathai Young', 'unique'=>true ),
			'active' => Array(),
			'emp_tp' => Array( 'required' =>true, 'label' => 'Type', 'default' => 'masseust',
					'enum' => Array( 'Masseust' => 'masseust','Housekeeping' => 'housekeeping','Reception' => 'reception') ),
			'disp_name' => Array( 'label'=>'Employee', 'updateable'=>false ) 
		),
	),
	'etp' => Array(
		'tbl' => 'exp_tp',
		'tbl_v' => 'exp_tp_v',
		'name' => 'Expense Types',
		'securityLv' => 4,
		'pkcol' => 'etp_id',
		'orderBy' => 'active desc, etp_cd',
		'cols' => Array(
			'etp_cd' => Array( 'required' =>true, 'unique' => true, 'label' => 'Code', 'validate' =>true ),
			'name' => Array(),
			'active' => Array(),
		),
	),
	'skl' => Array(
		'tbl' => 'skill',
		'tbl_v' => 'skill_v',
		'name' => 'Skill Types',
		'securityLv' => 4,
		'pkcol' => 'skl_id',
		'orderBy' => 'active desc, skl_cd',
		'cols' => Array(
			'skl_cd' => Array( 'required' =>true, 'unique' => true, 'label' => 'Code', 'validate' =>true ),
			'name' => Array(),
			'active' => Array(),
		),
	),
	'mnu' => Array(
		'tbl' => 'menu',
		'tbl_v' => 'menu_v',
		'name' => 'Service Offerings',
		'securityLv' => 3,
		'pkcol' => 'mnu_id',
		'orderBy' => 'active desc, mnu_sq',
		'lookups' => Array( 'mnu_sq'=>'exact', 'name'=>'partial' ),
		'cols' => Array(
			'mnu_sq' => Array( 'required' =>true, 'datatype' => 'number', 'unique' => true, 'label' => 'Sequence', 'validate' =>true ),
			'name' => Array(),
			'active' => Array(),
			'name_en' => Array( 'label' => 'English Name' ),
			'name_th' => Array( 'label' => 'Thai Name' ),
			'name_jp' => Array( 'label' => 'Japanese Name' ),
			'name_kn' => Array( 'label' => 'Korean Name' ),
			'price' => Array( 'datatype' => 'number', 'label' => 'Price', 'required' =>true, 'class'=>'money', 'default'=>200 ),
			'rate' => Array( 'datatype' => 'number', 'label' => 'Rate', 'required' =>true, 'class'=>'money', 'default'=>85 ),
			'cost' => Array( 'datatype' => 'number', 'label' => 'Cost', 'required' =>true, 'class'=>'money', 'default'=>15 ),
			'skl_id' => Array( 'datatype' => 'number', 'required' =>true, 'default'=>1,
					'fk' => Array( 'dd'=>'skl', 'pkcol'=>'skl_id', 'name'=>'skl_cd' ) ),
				'skl_cd' => Array( 'fkcol'=>'skl_id' ),
				'skl_nm' => Array( 'fkcol'=>'skl_id', 'refCol'=>'name' ),
			'disp_name' => Array( 'label' => 'Menu Item', 'updateable'=>false )
		),
	),
	'sgn' => Array(
		'tbl' => 'signin',
		'tbl_v' => 'signin_v',
		'name' => 'Employee Signin',
		'securityLv' => 2,
		'pkcol' => 'sgn_id',
		'orderBy' => 'from_ts desc, emp_nm',
		'cols' => Array(
			'emp_id' => Array( 'required' =>true, 'datatype' => 'number',
					'fk' => Array( 'dd'=>'emp', 'pkcol'=>'emp_id', 'name'=>'disp_name' ) ),
				'emp_nm' => Array( 'required' =>true, 'lookup'=>'emp_id', 'label' => 'Employee', 'updateable'=>false,
					'fkcol'=>'emp_id', 'refCol'=>'disp_name', 'depon'=>'emp_id' ),
			'from_ts' => Array( 'datatype'=>'datetime', 'label'=>'Sign In Date/Time', 'required'=>true, 'calc'=>'from_dt+" "+from_tm' ),
				'from_dt' => Array( 'datatype'=>'date', 'helper'=>true, 'label'=>'Sign In Date', 'updateable'=>false, 'depon'=>'from_ts' ),
				'from_tm' => Array( 'datatype'=>'time', 'helper'=>true, 'required'=>true, 'label'=>'Sign In Time',
							'updateable'=>false, 'depon'=>'from_ts', 'orderCol'=>'from_ts' ),
			'thru_ts' => Array( 'datatype'=>'datetime', 'label'=>'Sign Out Date/Time', 'required'=>false, 'calc'=>'thru_dt+" "+thru_tm' ),
				'thru_dt' => Array( 'datatype'=>'date', 'helper'=>true, 'label'=>'Sign Out Date', 'updateable'=>false, 'depon'=>'thru_ts' ),
				'thru_tm' => Array( 'datatype'=>'time', 'helper'=>true, 'label'=>'Sign Out Time', 'updateable'=>false, 'depon'=>'thru_ts', 'orderCol'=>'thru_ts' )
		)
	),
	'vst' => Array(
		'tbl' => 'visit',
		'tbl_v' => 'visit_v',
		'name' => 'Visit',
		'securityLv' => 1,
		'pkcol' => 'vst_id',
		'orderBy' => 'vst_dt,vst_id',
		'lookups' => Array( 'vst_id'=>'exact' ),
		'cols' => Array(
			'vst_dt' => Array( 'label'=>'Date', 'required'=>'true', 'notnull'=>true ),
			'voided' => Array( 'datatype'=>'boolean', 'label'=>'Voided', 'notnull'=>true, 'default'=>0 ),
			'nationality' => Array( 'label'=>'Nationality', 'default'=>null,
				'enum'=>Array( 'Unknown'=>null, 'Kon Thai'=>'thai', 'Farang'=>'farang', 'Japanese'=>'japan', 'Korean'=>'korea', 'Indian'=>'india' ) ),
			'idv_ct' => Array( 'label'=>'How Many', 'default'=>1 ),
			'amount' => Array( 'datatype'=>'money', 'label'=>'Total', 'notnull'=>true, 'default'=>0 ),
			'paid' => Array( 'datatype'=>'boolean', 'label'=>'Paid', 'notnull'=>true, 'default'=>0 )
		)
	),
	'svc' => Array(
		'tbl' => 'service',
		'tbl_v' => 'service_v',
		'name' => 'Service',
		'securityLv' => 1,
		'pkcol' => 'svc_id',
		'orderBy' => 'vst_dt, vst_id',
		'cols' => Array(
			'vst_id' => Array( 'label'=>'Visit No', 'fk' => Array( 'dd'=>'vst', 'pkcol'=>'vst_id', 'name'=>'vst_id' ) ),
				'vst_dt' => Array( 'fkcol'=>'vst_id' ),
				'vst_voided' => Array( 'fkcol'=>'vst_id', 'refCol'=>'voided' ),
				'nationality' => Array( 'fkcol'=>'vst_id' ),
				'idv_ct' => Array( 'fkcol'=>'vst_id' ),
				'amount' => Array( 'fkcol'=>'vst_id', 'label'=>'Grand Total' ),
				'paid' => Array( 'fkcol' => 'vst_id' ),
			'mnu_id' => Array( 'required' =>true, 'fk' => Array( 'dd'=>'mnu', 'pkcol'=>'mnu_id', 'name'=>'name' ) ),
				'mnu_nm' => Array( 'required'=>true, 'label' => 'Menu Item', 'updateable'=>false,
						'fkcol'=>'mnu_id', 'refCol'=>'disp_name' ),
				'price' => Array( 'required'=>true, 'default'=>0, 'fkcol'=>'mnu_id', 'doCalc' => Array( 'ttl_price' ) ),
				'cost' => Array( 'required'=>true, 'default'=>0, 'fkcol'=>'mnu_id', 'doCalc' => Array( 'ttl_cost' ) ),
				'rate' => Array( 'required'=>true, 'default'=>0, 'fkcol'=>'mnu_id', 'doCalc' => Array( 'ttl_rate' ) ),
			'emp_id' => Array( 'required' =>false, 'fk' => Array( 'dd'=>'emp', 'pkcol'=>'emp_id', 'name'=>'disp_name' ) ),
				'emp_nm' => Array( 'lookup'=>'emp_id', 'required' =>false, 'label' => 'Employee', 'updateable'=>false,
						'fkcol'=>'emp_id', 'refCol'=>'disp_name' ),
			'from_ts' => Array( 'datatype'=>'datetime', 'label'=>'Sign In Date/Time', 'required'=>true ),
			'from_tm' => Array( 'datatype'=>'time', 'required'=>true, 'label'=>'Sign In Time',
						'updateable'=>false, 'depCol'=>'from_ts', 'orderCol'=>'from_ts' ),
			'thru_ts' => Array( 'datatype'=>'datetime', 'label'=>'Sign Out Date/Time', 'required'=>false ),
			'thru_tm' => Array( 'datatype'=>'time', 'label'=>'Sign Out Time', 'updateable'=>false, 'depCol'=>'thru_ts', 'orderCol'=>'thru_ts' ),
			'qty' => Array( 'datatype'=>'number', 'precision'=>'1', 'required'=>true, 'default'=>1, 'step'=>'0.5',
						'doCalc' => Array( 'ttl_price', 'ttl_cost', 'ttl_rate' ) ),
			'ttl_price' => Array( 'datatype'=>'number', 'class'=>'money', 'required'=>true, 'default'=>0, 'calc'=>'qty * price' ),
			'ttl_cost' => Array( 'datatype'=>'number', 'class'=>'money', 'required'=>true, 'default'=>0, 'calc'=>'qty * cost' ),
			'ttl_rate' => Array( 'datatype'=>'number', 'class'=>'money', 'required'=>true, 'default'=>0, 'calc'=>'qry * rate' )
		)
	)
);

// get existent dd
$dd = unserialize(file_get_contents('../ajax/dd.ser'));

$client_only = Array( 'class' );
$server_only = Array( 'notnull', 'fk' );

function reset_users( $schema ) {
GLOBAL $db_params;

	if( !$schema ) return;

	// developer login
	$db = mysql_connect( 'localhost', 'massage150', 'ForAGoodTimeCrawl' );
	mysql_select_db( $schema, $db );

	// set up the login and change_password procs
	$procs = file_get_contents( '../sql/security.sql' );
	$procArr = preg_split( "/delimiter .*\n/", $procs );
	foreach( $procArr as $stmt ) {
		$stmt = str_replace( '$$', '', $stmt );
		if( trim($stmt) == '' ) continue;
		mysql_query( $stmt );
		$err = mysql_error($db);
		if( $err ) ajax_exit( 'failure', "mysql: $stmt => $err" );
	}

	// (re)create users
	$users = $db_params['dbUsers'];
	// create users as necessary, reset password
	foreach( $users as $user=>$arr ) mysql_query( "drop user {$arr['user']}" ); 
	// mysql kludge so user info does not get broken
	mysql_query( "flush privileges" );
	foreach( $users as $user=>$arr ) mysql_query( "create user {$arr['user']} identified by '{$arr['pwd']}'" );
	// list of tables
	$result = mysql_query( "show tables", $db );
	while( $rcd = mysql_fetch_assoc( $result ) )
	foreach( $rcd as $table ) $tables[] = $table;
	
	// for each user set permission
	foreach( $users as $user=>$arr ) {
		if( @$arr['table_grant'] ) foreach( $tables as $table ) mysql_query( "grant {$arr['table_grant']} on $table to {$arr['user']}" );
		if( @$arr['tables_revoke'] ) foreach( $arr['tables_revoke'] as $table ) mysql_query( "revoke {$arr['table_grant']} on $table from {$arr['user']}" );
		if( @$arr['procedures_grant'] ) foreach( $arr['procedures_grant'] as $proc ) mysql_query( "grant execute on procedure $proc to {$arr['user']}" );
		if( @$arr['functions_grant'] ) foreach( $arr['functions_grant'] as $func ) mysql_query( "grant execute on function $func to {$arr['user']}" );
	}
}

if( true || $dd['_version'] < $dd_ver ) {

	reset_users( 'suangao' );

	// our target
	$dd = Array( '_version'=>$dd_ver );

	// go through rel's
	foreach( $dd_raw as $rel => $rattrs ) {
		// pull out defaults
		if( $rel == '*' ) {
			$dflt_names = $rattrs['col'];
			$dflt_datatypes = $rattrs['datatype'];
			continue;
		}
		// extract each rel
		$dd[$rel] = Array( 'cols'=>Array() );
		// extract each col
		foreach( $rattrs as $rattr=>$rval ) {

			// meta
			if( $rattr != 'cols' )  $dd[$rel][$rattr] = $rval;
			
			// cols
			else if( $rattr == 'cols' ) foreach( $rval as $col=>$cattrs ) {

				// base col
				$dd[$rel]['cols'][$col] = Array();
				$attrs = Array();

				// fkcol, refCol
				if( isset($cattrs['fkcol']) ) {
					$fkcol = $cattrs['fkcol'];
					$refDD = $dd[$rel]['cols'][$fkcol]['fk']['dd'];
					$refCol = (isset($cattrs['refCol'])) ? $cattrs['refCol'] : $col;
					// inhert ref col attrs
					foreach( $dd[$refDD]['cols'][$refCol] as $attr=>$val ) {
						if( !isset($cattrs[$attr]) ) $cattrs[$attr] = $val;
					}
					// add this to fk's cols
					$dd[$rel]['cols'][$fkcol]['fk']['cols'][] = Array( 'renderCol'=>$col, 'queryCol'=>$refCol );
				}

				// name defaults
				foreach( $dflt_names as $nregx=>$nattrs ) 
				if( preg_match("/$nregx/", $col ) ) {
					//-echo "$col match $nregx: ".implode(',',$nattrs)."\n";
					foreach( $nattrs as $k=>$v )  $attrs[$k] = $v;
				}
				//-echo "col=$col, attrs=".implode(',',$attrs);
				// datatype defaults
				$datatype = @$cattrs['datatype'];
				if( !$datatype ) $datatype = @$attrs['datatype'];
				//-echo " datatype=$datatype ";
				// put together - datatype defaults, name defaults, col attrs
				if( $datatype && isset($dflt_datatypes[$datatype]) )
					foreach( $dflt_datatypes[$datatype] as $k=>$v ) $dd[$rel]['cols'][$col][$k] = $v;
				foreach( $attrs as $k=>$v ) $dd[$rel]['cols'][$col][$k] = $v;
				foreach( $cattrs as $cattr=>$cval ) $dd[$rel]['cols'][$col][$cattr] = $cval;
				//-echo "final=".implode(',',$dd[$rel]['cols'][$col])."\n";
			}
		}
	}

	// write to json
	file_put_contents( '../ajax/dd.json', json_encode($dd,JSON_FORCE_OBJECT) );
	file_put_contents( '../ajax/dd.ser', serialize($dd) );
}

?>
